package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Refund;
import com.houdoudou.bean.Refund;
import com.houdoudou.common.DateTimeFunction;

public class RefundDao {

	 Connection conn=null;	


		public RefundDao(Connection conn) {
			super();
			this.conn = conn;
		}

		public  void create( Refund refund ) throws SQLException{	
			long result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "insert  into refund ( orderID ,type ,state ,money ,result ,reason ,dateTimeCP  ,dateTimeS ,dateTimeSL ,dateTimeC ," //10
		        			+ "dateTimeCL,dateTimeO ,dateTimeOL,dateTimeCC,owner,supplier ) " //6个
		        + "values "
		        + "(?,?,?,?,?,?,?,?,?,?,"
		        + " ?,?,?,?,?,?)";
		        	//共16个参数。
		        	pstm=conn.prepareStatement(sql);
		        	pstm.setLong(1, refund.getOrderID());
		        	pstm.setInt(2, refund.getType());
		        	pstm.setInt(3, refund.getState());  
		        	pstm.setDouble(4, refund.getMoney()); 
		        	pstm.setInt(5, refund.getResult());  
		        	pstm.setString(6, refund.getReason());
	        			   
		        	pstm.setString(7, refund.getDateTimeCP());
		        	pstm.setString(8, refund.getDateTimeS());  
		        	pstm.setString(9,  refund.getDateTimeSL()); 
		        	pstm.setString(10, refund.getDateTimeC()); 
		        	pstm.setString(11, refund.getDateTimeCL()); 
		        	pstm.setString(12, refund.getDateTimeO()); 
		        		        	
		        	pstm.setString(13, refund.getDateTimeOL()); 
		        	pstm.setString(14, refund.getDateTimeCC()); 
		        	pstm.setString(15, refund.getOwner()); 
		        	pstm.setString(16, refund.getSupplier()); 
		        	pstm.executeUpdate();
		        	
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			
		}
		
		private String getDeleteSql(Refund refund){
			String sql="delete  from refund ";

			int count=0;
			if(refund.getOrderID()!=-1){
				 sql+="where orderID="+ refund.getOrderID();
			}
			
			System.out.print(sql);
			return sql;
		}
		public  void delete(Refund refund) throws SQLException{	
			
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = getDeleteSql(refund);		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
					stm.executeUpdate(sql);
					
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			
		}
		
		
		
		public  int update( Refund refund ) throws SQLException, Exception{	
			  int result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 		
			// 拼凑 sql.			
		    //sql = "update sort set name=?,display=? where id = ?";
			
	       	
	      
			   try {
				   int whereCount=1;		
					int setCount=1;	
					int count=1;
			String sql="update refund  ";	
			if(refund.getState()!=-1) {			
				            if(setCount==1) {sql= sql+" set state = "+refund.getState();  
				            }else{sql= sql+" , state = "+refund.getState();}			
				            setCount++;   }
			if(refund.getResult()!=-1) {			
				if(setCount==1) {sql= sql+" set result = "+refund.getResult();
				           }else{sql= sql+" , result = "+refund.getResult();}			
				            setCount++;  }
			
			if(refund.getMoney()!=-1) {			
				if(setCount==1) {sql= sql+" set money = "+refund.getMoney();
				           }else{sql= sql+" , money = "+refund.getMoney();}			
				            setCount++;  }
				    	
	    	if(!refund.getDateTimeS().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeS = "+refund.getDateTimeS();
				           }else{sql= sql+" , dateTimeS = "+refund.getDateTimeS();}			
				            setCount++;  }
	     	if(!refund.getDateTimeC().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeC = "+refund.getDateTimeC();
				           }else{sql= sql+" , dateTimeC = "+refund.getDateTimeC();}			
				            setCount++;  }
	    	if(!refund.getDateTimeCL().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeCL = "+refund.getDateTimeCL();
				           }else{sql= sql+" , dateTimeCL = "+refund.getDateTimeCL();}			
				            setCount++;  }

	    	if(!refund.getDateTimeO().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeO = "+refund.getDateTimeO();
				           }else{sql= sql+" , dateTimeO = "+refund.getDateTimeO();}			
				            setCount++;  }
	    	if(!refund.getDateTimeOL().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeOL = "+refund.getDateTimeOL();
				           }else{sql= sql+" , dateTimeOL = "+refund.getDateTimeOL();}			
				            setCount++;  }
	    	if(!refund.getDateTimeCC().isEmpty()) {			
				if(setCount==1) {sql= sql+" set dateTimeCC = "+refund.getDateTimeCC();
				           }else{sql= sql+" , dateTimeCC = "+refund.getDateTimeCC();}			
				            setCount++;  }
	    	
	    	if(!refund.getReason().isEmpty()) {			
				if(setCount==1) {sql= sql+" set reason ="+"\'"+refund.getReason()+"\'";
				           }else{sql= sql+" , reason ="+"\'"+refund.getReason()+"\'";}			
				            setCount++;  }
	    	
	    	
			if(refund.getOrderID()!=-1) {	
				        if(whereCount==1) {sql= sql+"  where orderID = "+refund.getOrderID();  }else{sql= sql+" and  orderID = "+refund.getOrderID(); }  
				          whereCount++; 
		    }else{ throw new Exception("更新订单，没传订单编号");}
			
			 //---------set-------------
			System.out.println(sql);
			stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			stm.executeUpdate(sql);			
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return result;
		}
		
		 public static  String  getSearchSql(Refund refund){
				String sql="select * from refund";
				int count=1;		
				
				if(refund.getOrderID()!=-1) {	
					if(count==1) {sql= sql+" where orderID = "+refund.getOrderID();
					         }else{	sql= sql+" and  orderID = "+refund.getOrderID(); }  
					            count++; return sql; }
		
				if(refund.getState()!=-1) {	
					if(count==1) {sql= sql+" where state = "+refund.getState();
					          }else{sql= sql+" and  state = "+refund.getState(); }  
					            count++; }
				if(refund.getType()!=-1) {	
					if(count==1) {sql= sql+" where type= "+refund.getType();
					          }else{sql= sql+" and  type = "+refund.getType(); }  
					            count++; }
				if(refund.getResult()!=-1) {	
					if(count==1) {sql= sql+" where result = "+refund.getResult();
					          }else{sql= sql+" and  result = "+refund.getResult(); }  
					            count++; }
				if(!refund.getSupplier().isEmpty()) {	
					if(count==1) {sql= sql+" where supplier = "+refund.getSupplier();
					          }else{sql= sql+" and supplier = "+refund.getSupplier(); }  
					            count++; }
				if(!refund.getOwner().isEmpty()) {	
					if(count==1) {sql= sql+" where owner = "+refund.getOwner();
					          }else{sql= sql+" and owner = "+refund.getOwner(); }  
					            count++; }
				
			System.out.println(sql);
				return sql;
			}

		public List< Refund> search( String sql) throws SQLException{	
			List< Refund> refunds= new ArrayList<Refund>();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
		
		        try {        		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					while(rs.next()){
						Refund refund = new Refund();
						refund.setOrderID(rs.getLong("orderID"));
						refund.setState(rs.getInt("state"));
			        	refund.setType(rs.getInt("type"));
			        	refund.setMoney(rs.getDouble("money"));  
			        	refund.setResult(rs.getInt("result")); 
		        	
			        	refund.setReason(rs.getString("reason"));
			        	refund.setDateTimeCP(rs.getString("dateTimeCP"));
			        	refund.setDateTimeS(rs.getString("dateTimeS"));  
			        	refund.setDateTimeSL(rs.getString("dateTimeSL")); 
			        	refund.setDateTimeC(rs.getString("dateTimeC")); 
			        	refund.setDateTimeCL(rs.getString("dateTimeCL")); 
			        	refund.setDateTimeO(rs.getString("dateTimeO")); 
			        	refund.setDateTimeOL(rs.getString("dateTimeOL")); 
			        	refund.setDateTimeCC(rs.getString("dateTimeCC")); 			        		        	
			        	
			        	refund.setSupplier(rs.getString("supplier"));
			        	refund.setSupplier(rs.getString("owner"));
						refunds.add(refund);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return refunds;
		}	
		
		 public Refund selectById(long orderID) throws Exception{
			   String sql="select * from refund  where orderID = "+ orderID;
			   System.out.println(sql);
			   List<Refund> refunds= search(sql);
			   Refund refundExist = null;
			   if(refunds.size()>0){ refundExist= refunds.get(0); 
			   }
			   return refundExist;
		   }
}
